Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# CREATE PACKAGE BODY

Purpose

Use the `CREATE` `PACKAGE` `BODY` statement to create the body of a stored package, which is an encapsulated collection of related procedures, stored functions, and other program objects stored together in the database. The package body defines these objects. The package specification, defined in an earlier `CREATE` `PACKAGE` statement, declares these objects.

Packages are an alternative to creating procedures and functions as standalone schema objects.

See Also: CREATE FUNCTION and CREATE PROCEDURE for information on creating standalone functions and procedures CREATE PACKAGE for a discussion of packages, including how to create packages "Examples" for some illustrations ALTER PACKAGE for information on modifying a package DROP PACKAGE for information on removing a package from the database

Prerequisites ``

Before a package can be created, the user `SYS` must run a SQL script commonly called `DBMSSTDX.SQL`. The exact name and location of this script depend on your operating system.

To create a package in your own schema, you must have `CREATE` `PROCEDURE` system privilege. To create a package in another user's schema, you must have `CREATE` `ANY` `PROCEDURE` system privilege.

To embed a `CREATE` `PACKAGE` `BODY` statement inside an Oracle Database precompiler program, you must terminate the statement with the keyword `END-EXEC` followed by the embedded SQL statement terminator for the specific language.

See Also: PL/SQL User's Guide and Reference

Syntax

create_package_body::=

Description of the illustration create_package_body.gif

Semantics

OR REPLACE

Specify `OR` `REPLACE` to re-create the package body if it already exists. Use this clause to change the body of an existing package without dropping, re-creating, and regranting object privileges previously granted on it. If you change a package body, then Oracle Database recompiles it.

Users who had previously been granted privileges on a redefined package can still access the package without being regranted the privileges.

See Also: ALTER PACKAGE for information on recompiling package bodies

schema

Specify the schema to contain the package. If you omit `schema`, then the database creates the package in your current schema.

package

Specify the name of the package to be created.

pl/sql_package_body

Specify the package body, which can contain PL/SQL subprogram bodies or call specifications, which are declarations of a C or Java routine expressed in PL/SQL.

See Also: Oracle Database Application Developer's Guide - Fundamentals for more information on writing PL/SQL or C package program units Oracle Database Java Developer's Guide for information on Java package program units "Restrictions on User-Defined Functions" for a list of restrictions on user-defined functions in a package

Examples

Creating a Package Body: Example This SQL statement creates the body of the `emp_mgmt` package created in "Creating a Package: Example". The PL/SQL is shown in italics:

    CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
       tot_emps NUMBER;
       tot_depts NUMBER;
    FUNCTION hire
       (last_name VARCHAR2, job_id VARCHAR2,
        manager_id NUMBER, salary NUMBER,
        commission_pct NUMBER, department_id NUMBER)
       RETURN NUMBER IS new_empno NUMBER;
    BEGIN
       SELECT employees_seq.NEXTVAL
          INTO new_empno
          FROM DUAL;
       INSERT INTO employees
          VALUES (new_empno, 'First', 'Last','first.last@oracle.com',
                  '(123)123-1234','18-JUN-02','IT_PROG',90000000,00,
                  100,110);
          tot_emps := tot_emps + 1;
       RETURN(new_empno);
    END;
    FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
       RETURN NUMBER IS
          new_deptno NUMBER;
       BEGIN
          SELECT departments_seq.NEXTVAL
             INTO new_deptno
             FROM dual;
          INSERT INTO departments
             VALUES (new_deptno, 'department name', 100, 1700);
          tot_depts := tot_depts + 1;
          RETURN(new_deptno);
       END;
    PROCEDURE remove_emp (employee_id NUMBER) IS
       BEGIN
          DELETE FROM employees
          WHERE employees.employee_id = remove_emp.employee_id;
          tot_emps := tot_emps - 1;
       END;
    PROCEDURE remove_dept(department_id NUMBER) IS
       BEGIN
          DELETE FROM departments
          WHERE departments.department_id = remove_dept.department_id;
          tot_depts := tot_depts - 1;
          SELECT COUNT(*) INTO tot_emps FROM employees;
       END;
    PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS
       curr_sal NUMBER;
       BEGIN
          SELECT salary INTO curr_sal FROM employees
          WHERE employees.employee_id = increase_sal.employee_id;
          IF curr_sal IS NULL
             THEN RAISE no_sal;
          ELSE
             UPDATE employees
             SET salary = salary + salary_incr
             WHERE employee_id = employee_id;
          END IF;
       END;
    PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS
       curr_comm NUMBER;
       BEGIN
          SELECT commission_pct
          INTO curr_comm
          FROM employees
          WHERE employees.employee_id = increase_comm.employee_id;
          IF curr_comm IS NULL
             THEN RAISE no_comm;
          ELSE
             UPDATE employees
             SET commission_pct = commission_pct + comm_incr;
          END IF;
       END;
    END emp_mgmt;
    /

The package body defines the public program objects declared in the package specification:

- The functions `hire` and `create_dept`

- The procedures `remove_emp`, `remove_dept`, `increase_sal`, and `increase_comm`

These objects are declared in the package specification, so they can be called by application programs, procedures, and functions outside the package. For example, if you have access to the package, you can create a procedure `increase_all_comms` separate from the `emp_mgmt` package that calls the `increase_comm` procedure.

These objects are defined in the package body, so you can change their definitions without causing Oracle Database to invalidate dependent schema objects. For example, if you subsequently change the definition of `hire`, then the database need not recompile `increase_all_comms` before executing it.

The package body in this example also declares private program objects, the variables `tot_emps` and `tot_depts`. These objects are declared in the package body rather than the package specification, so they are accessible to other objects in the package, but they are not accessible outside the package. For example, you cannot develop an application that explicitly changes the value of the variable `tot_depts`. However, the function `create_dept` is part of the package, so `create_dept` can change the value of `tot_depts`.